﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using FreeOnlineApplication.Class.Entity;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Collections;

namespace FreeOnlineApplication.Class.Repository
{
    public class RoomMasterRepository
    {
        protected MySqlConnection m_connection;
        MySqlCommand aCommand = new MySqlCommand();
        RoomTypeRepository m_roopTypeRep;

        public RoomMasterRepository(MySqlConnection connection)
        {
            m_connection = connection;
            aCommand.Connection = m_connection;
            m_roopTypeRep = new RoomTypeRepository(connection);
        }
        public void Insert(RoomMaster roommaster)
        {
            string insertSQL = String.Format(@"insert into table_roommaster 
                (
                    roommaster_code,
                    roomtype_id,
                    unit_id,
                    roommaster_tariff,
                    roommaster_floor) 
                VALUES ('{0}','{1}',{2},{3},{4})",
                roommaster.CODE, roommaster.ROOM_TYPE.ID, roommaster.UNIT.ID,roommaster.TARIFF
                ,roommaster.FLOOR);
            aCommand.CommandText = insertSQL;
            aCommand.ExecuteNonQuery();
            roommaster.ID = GetMaximumId();
        }
        public RoomMaster GetById(int id)
        {
            string getByID = String.Format("select * from table_roommaster where roommaster_id = {0}", id);
            aCommand.CommandText = getByID;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            RoomMaster roommaster = transform(aReader);
            aReader.Close();
            return roommaster;
        }
        public void Update(RoomMaster roommaster)
        {
            string updateSQL = String.Format(@"update table_roommaster set 
                roommaster_code = '{0}', 
                roomtype_id='{1}',
                unit_id = {2},
                roommaster_tariff = {3},
                roommaster_floor = {4}
                where roommaster_id = {5}",
                roommaster.CODE, roommaster.ROOM_TYPE.ID, roommaster.UNIT.ID
                , roommaster.TARIFF, roommaster.FLOOR, roommaster.ID);
            aCommand.CommandText = updateSQL;
            aCommand.ExecuteNonQuery();
        }
        public void Delete(RoomMaster roommaster)
        {
            string deleteSQL = "delete from table_roommaster where roommaster_id = " + roommaster.ID;
            aCommand.CommandText = deleteSQL;
            aCommand.ExecuteNonQuery();
        }
        public int GetMaximumId()
        {
            string getByID = String.Format("SELECT max(roommaster_id) from table_roommaster");
            aCommand.CommandText = getByID;
            object r = aCommand.ExecuteScalar();
            int aReader = r is System.DBNull ? 0 : Convert.ToInt32(r);
            return aReader;
        }
        public int GetCount(int unit)
        {
            string getByID = String.Format("SELECT count(*) from table_roommaster where unit_id = {0}", unit);
            aCommand.CommandText = getByID;
            object r = aCommand.ExecuteScalar();
            int aReader = r is System.DBNull ? 0 : Convert.ToInt32(r);
            return aReader;
        }
        public IList GetAll(int roommaster)
        {
            string sql = String.Format("select * from table_roommaster where unit_id = {0}", roommaster);
            aCommand.CommandText = sql;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            IList users = transformList(aReader);
            aReader.Close();
            return users;
        }
        public IList GetAllByRoomType(int roomType)
        {
            string sql = String.Format("select * from table_roommaster where roomtype_id = {0}", roomType);
            aCommand.CommandText = sql;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            IList users = transformList(aReader);
            aReader.Close();
            return users;
        }
        private RoomMaster transform(MySqlDataReader aReader)
        {
            RoomMaster roommaster = null;
            while (aReader.Read())
            {
                roommaster = new RoomMaster();
                roommaster.ID = Convert.ToInt32(aReader["roommaster_id"]);
                roommaster.CODE = aReader["roommaster_code"].ToString();
                roommaster.ROOM_TYPE = new RoomType(Convert.ToInt32(aReader["roomtype_id"]));// m_roopTypeRep.GetById(Convert.ToInt32(aReader["roomtype_id"]));
                roommaster.UNIT = new Unit(Convert.ToInt32(aReader["unit_id"]));
                roommaster.TARIFF = Convert.ToDouble(aReader["roommaster_tariff"]);
                roommaster.FLOOR = Convert.ToDouble(aReader["roommaster_floor"]);
            }
            return roommaster;
        }
        private IList transformList(MySqlDataReader aReader)
        {
            IList result = new ArrayList();
            while (aReader.Read())
            {
                RoomMaster roommaster = new RoomMaster();
                roommaster.ID = Convert.ToInt32(aReader["roommaster_id"]);
                roommaster.CODE = aReader["roommaster_code"].ToString();
                roommaster.ROOM_TYPE = new RoomType(Convert.ToInt32(aReader["roomtype_id"]));// m_roopTypeRep.GetById(Convert.ToInt32(aReader["roomtype_id"]));
                roommaster.UNIT = new Unit(Convert.ToInt32(aReader["unit_id"]));
                roommaster.TARIFF =Convert.ToDouble(aReader["roommaster_tariff"]);
                roommaster.FLOOR = Convert.ToDouble(aReader["roommaster_floor"]);
                result.Add(roommaster);
            }
            return result;
        }
    }
}
